Back to Main Menu

Bulk Import and Processing of In-year Treatments

The following process demonstrates how to bulk update existing assets within the Asset Register that have been modified (renewal, upgrade and disposal) as a result of a Capital Works Program for the year.

Import File

Prepare a separate Excel spreadsheet import file with the following headings for each Asset Category that has treatments to be imported.

In a spreadsheet like the above, populate data in the following fields:

Column Name	Details
Treatment ID	Leave this blank as the import process will auto-generate a new Treatment ID.
Parent Asset Category	The Asset Category for the asset being treated.
Parent Asset ID	The Asset ID of the asset being treated.
Component Name	The component of the asset that the treatment expenditure refers to. E.g. For Roads, the name can be Surface, Pavement Base, Pavement Subbase, Formation, etc. Note: The component must already exist in the Fair Value Tab. i.e. Must have Date of Recognition, Recognition Type and Valuation Date (may contain a zero value). Example: If a seal is applied to an unsealed road as part of the Capital Expenditure, it is necessary to create the surface component in Fair Value before loading the treatment.
Actual Treatment Date	The date when the asset was treated. This date must be within the current Financial Year, but later than the existing valuation date. If there is more than 1 treatment to a component the treatment date must be different by at least 1 day. Only use the 30th of June if there will be only one treatment for the year.
Date Built	The actual treatment date, earlier, or left blank. Note: In myData v1.9.3, this field is mandatory.
Treatment Job Number	This can be a Job number, Work Order number, or any reference number allocated to this project.
Actual Treatment	E.g. Renewal.
Actual Treatment Type	E.g. Reseal.
Actual Cost ($)	This is the total amount spent.
Capital Addition ($)	This is the net amount spend for the works (Items like site clearance, rubbish removal etc. can be deducted from total spend to get the net spend). Capital addition must not exceed the Actual Cost.
Disposal Option	Generally, in such transactions, if an asset or component of an asset has been renewed, we need to dispose of either the entire asset or a part of the asset and also apply capital additions to it. Options are: 0, 1, 2, 3 or it can be left Blank which is equivalent to no disposal. Option 0 = no disposal A partial disposal, for example; dispose of 33% of the current RV, is unable to be achieved in this bulk import process. The user needs to manually apply an in-year treatment for partial disposal.
Useful Life	Depends on the type of treatment and quantity of asset treated. (E.g. If most of the asphalt footpath has been replaced by concrete footpath, the useful life of the asset can improve). Note: Leave Blank if there is no change.
Valuation Matrix	The Valuation Matrix that applies to the component. Notes: The Valuation Matrix must already exist in myData. Leave blank if the existing valuation matrix applies.

Note: For the treatments, the SPI will update automatically based on the values in the import.

Import the Treatments

Select 'Data Integration' on the top Menu bar.

Then select 'Import Data'.

A window will open, select 'Treatments'.

Browse and select the source file to be imported in.

Select Next.

Do not uncheck “Do not overwrite existing data with empty values”, as this will erase existing data.

Check whether the appropriate columns are mapped.

Then select Next.

A preview of the changes is displayed. Once the user is satisfied, press 'OK'.

Then 'Finish'.

The treatments will be listed for each asset under the Treatments Tab.

Bulk Process the Treatments

To bulk process the treatments, go to 'Asset Register' from the menu bar and then 'Bulk Treatment Process'.

Select all the treatments via the checkboxes.

Once all selected, click on 'Process'.

Note: The processing time should be roughly about 2 seconds per treatment.

Check if all the Treatments were processed by opening the Bulk Treatment Process. The list should be all clear.

If a treatment fails to process, they will still be in the Bulk Treatment Process list.

 

Select all treatments except for the first on the list and rerun the Bulk Process.

 

Go to the Bulk Process again and check if any other treatments have failed.

 

Check the details of the failed treatments for any obvious data issues.

 

Reasons for the treatment to fail to process may be due to errors in the data import, such as:

• Component Name may not be a valid option
• The component may not exist in Fair Value
• Treatment date (must be within the current year and greater then valuation date and any other treatments for the year for the asset component)
• The actual cost must be equal to or greater than Capital Addition
• Disposal options not = 0,1,2,3 or left blank
• Valuation Matrix listed in the data file may not exist in myData

 

For the failed treatment/s, go to the relevant asset/s and delete the failed treatment/s.  Do not delete any treatments that have been processed. 

 

For the failed treatment/s go to the treatment import data file and fix any data errors, create a new file or Tab and reimport the failed treatment/s.

Then rerun the Bulk Treatment Process.

Results

Run the 'Financial Reconciliation' and 'At Cost Transaction' reports to check the results of the treatments.